package com.dyj.ads

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SparkSession}
object ads_e_mz_ave7662_bxb_hxb_xfc {
  def main(args: Array[String]): Unit = {
    val ds: String = args(0)
    /**
     *1：求白细胞与均值差列为新的一列
     *2：求红细胞与均值差列为新的一列
     *3：1和2的乘积列为新的一列
     *4：将第3步的结果sum
     *5：将第4步的结果进行除与总数减1得到协方差
     *6：皮尔逊相关系数等于协方差除二者的标准差乘积之和
     */
    val sparkSession: SparkSession = SparkSession.builder()
      .appName("白细胞与红细胞的协方差以及皮尔逊相关系数")
      .enableHiveSupport()
      .config("spark.sql.shuffle.partitions", 1)
      .getOrCreate()

    import sparkSession.implicits._
    import org.apache.spark.sql.functions._

    sparkSession.sql("use bigdata03_ads")

    val xfcdf: DataFrame = sparkSession.sql(
      s"""
         |select
         |t1.baoGaoBianHao baoGaoBianHao,
         |t1.baoGaoRiQi baoGaoRiQi,
         |t1.baiXiBao baiXiBao,
         |t1.bxb_avg bxb_avg,
         |round(t1.bxb_bzc,2) bxb_bzc,
         |t2.hongXiBao hongXiBao,
         |t2.hxb_avg hxb_avg,
         |round(t2.hxb_bzc,2) hxb_bzc
         |from
         |(SELECT baoGaoBianHao, baoGaoRiQi, baiXiBao, bxb_avg, bxb_bzc FROM ads_e_mz_ave7662_bxb WHERE ds='${ds}') t1
         |join
         |(SELECT baoGaoBianHao, hongXiBao, hxb_avg, hxb_bzc FROM ads_e_mz_ave7662_hxb WHERE ds='${ds}') t2
         |on
         |t1.baoGaoBianHao = t2.baoGaoBianHao
         |""".stripMargin)

        xfcdf.withColumn("bxb_jzc",$"baiXiBao"-$"bxb_avg")
          .withColumn("hxb_jzc",$"hongXiBao"-$"hxb_avg")
          .withColumn("hxb_bxb_jzc_cj",$"bxb_jzc"*$"hxb_jzc")
          .withColumn("hxb_bxb_jzc_cj_sum",sum($"hxb_bxb_jzc_cj").over())
          .withColumn("number",count($"baoGaoBianHao").over())
          .withColumn("hxb_bxb_xfc",$"hxb_bxb_jzc_cj_sum" / ($"number" - 1))
          .withColumn("hxb_bxb_pexxs",$"hxb_bxb_xfc" / ($"bxb_bzc" * $"hxb_bzc"))
          .select($"bxb_bzc",$"hxb_bzc",$"hxb_bxb_xfc",$"hxb_bxb_pexxs")
          .distinct()
          .write
          .format("csv")
          .save("/daas/motl/bigdata03/ads/ads_e_mz_ave7662_bxb_hxb_xfc/ds=" + ds)


  }
}
